const express = require("express");
const router = express.Router();
const dayjs = require("dayjs");
const utc = require("dayjs/plugin/utc");
const timezone = require("dayjs/plugin/timezone");

dayjs.extend(utc);
dayjs.extend(timezone);

let connectDAO = require("./connect.js");

// 获取所有教师
router.get("/get_teachers", function (req, res) {
  const { TeacherID } = req.query;
  let sql = `SELECT * FROM Teacher`;
  if (TeacherID) {
    sql += ` WHERE TeacherID = '${TeacherID}'`;
  }
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.length > 0) {
      res.send({
        code: 200,
        msg: "Success",
        records,
        total: records.length,
      });
    } else {
      res.send({
        code: 200,
        msg: "no data",
      });
    }
  });
});

// 获取值班安排记录（带分页）
router.get("/get_duty_schedules", function (req, res) {
  // 解析参数并设置默认值
  const {
    page = 1,
    pageSize = 10,
    ShiftType,
    TeacherID,
    RoomID
  } = req.query;

  // 参数验证
  let pageNum = parseInt(page);
  let pageSizeNum = parseInt(pageSize);
  if (isNaN(pageNum)) pageNum = 1;
  if (isNaN(pageSizeNum)) pageSizeNum = 10;
  const offset = (pageNum - 1) * pageSizeNum;

  // 基础查询（保持您原有的联表方式）
  let baseSql = `
    SELECT ds.*, t.Name AS TeacherName 
    FROM DutySchedule ds
    LEFT JOIN Teacher t ON ds.TeacherID = t.TeacherID
  `;
  let countSql = `SELECT COUNT(*) AS total FROM DutySchedule ds`;
  const conditions = [];

  // 筛选条件（保持您原有的拼接方式）
  if (ShiftType) {
    conditions.push(`ds.ShiftType = '${ShiftType}'`);
  }
  if (TeacherID) {
    conditions.push(`ds.TeacherID = '${TeacherID}'`);
  }
  if (RoomID) {
    conditions.push(`ds.RoomID = '${RoomID}'`);
  }

  // 拼接WHERE条件
  let whereClause = '';
  if (conditions.length > 0) {
    whereClause = ' WHERE ' + conditions.join(' AND ');
    baseSql += whereClause;
    countSql += whereClause;
  }

  // 分页查询
  const dataSql = `${baseSql} LIMIT ${pageSizeNum} OFFSET ${offset}`;

  // 先查询总数
  connectDAO.queryDataBySQL(countSql, (countResult) => {
    const total = countResult[0].total;

    // 再查询分页数据
    connectDAO.queryDataBySQL(dataSql, (records) => {
      res.send({
        code: 200,
        msg: "Success",
        data: {
          records,
          pagination: {
            current: pageNum,
            pageSize: pageSizeNum,
            total: total
          }
        }
      });
    });
  });
});
// 新增值班记录
router.post("/add_duty_schedule", function (req, res) {
  const { TeacherID, ShiftType, Date, RoomID, RoomName } = req.body;
  let sql = `INSERT INTO DutySchedule (TeacherID, ShiftType, Date,RoomID,RoomName) VALUES ('${TeacherID}', '${ShiftType}', '${Date}','${RoomID}','${RoomName}')`;
  connectDAO.queryDataBySQL(sql, (records) => {
    res.send({
      code: 200,
      msg: "Success",
    });
  });
});

// 更新值班记录
router.put("/update_duty_schedule/:DutyScheduleID", function (req, res) {
  const { DutyScheduleID } = req.params;
  const { TeacherID, ShiftType, Date, RoomID, RoomName } = req.body;
  const sql = `UPDATE DutySchedule SET TeacherID = ${TeacherID}, ShiftType = '${ShiftType}', Date = '${Date}', RoomID = ${RoomID}, RoomName = '${RoomName}' WHERE DutyScheduleID = ${DutyScheduleID}`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.affectedRows > 0) {
      res.send({
        code: 200,
        msg: "Success",
      });
    } else {
      res.send({
        code: 200,
        msg: "no data",
      });
    }
  });
});

// 删除值班记录
router.delete("/delete_duty_schedule/:DutyScheduleID", function (req, res) {
  const { DutyScheduleID } = req.params;
  let sql = `DELETE FROM DutySchedule WHERE DutyScheduleID = '${DutyScheduleID}'`;
  connectDAO.queryDataBySQL(sql, (records) => {
    if (records.affectedRows > 0) {
      res.send({
        code: 200,
        msg: "Success",
      });
    } else {
      res.send({
        code: 200,
        msg: "no data",
      });
    }
  });
});

module.exports = router;
